﻿using iTool.Cloud.Center;
using iTool.Cloud.Database.KeyGeneratorProvider;
using iTool.Cloud.Database.KeyGeneratorProvider.Contract;
using iTool.Cloud.Database.LocalServiceProvider;
using iTool.Cloud.Database.Options;
using iTool.Cloud.Database.ServiceProvider;
using iTool.Cloud.Database.SqlStructureProvider;
using iTool.Cloud.DataSearch;
using iTool.Cloud.DataSearch.ServiceProvider;
using iTool.ClusterComponent;
using iTool.Clustering.Center;
using iTool.Common;
using iTool.SQL.AIHandle;

using Lucene.Net.Documents;
using Lucene.Net.Index;
using Lucene.Net.Util;

using Microsoft.Data.Sqlite;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Options;
using Microsoft.SqlServer.Management.SqlParser.Metadata;

using Orleans;
using Orleans.Concurrency;
using Orleans.Configuration;
using Orleans.Runtime;
using Orleans.Serialization.WireProtocol;
using Orleans.Versions.Selector;

using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.IO;
using System.Linq;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using System.Timers;

using static Lucene.Net.Documents.Field;
using static Lucene.Net.Util.Fst.Util;
using static System.Collections.Specialized.BitVector32;

using iUtils = iTool.Clustering.Center.iUtils;

namespace iTool.Cloud.Database.iToolService
{

    public abstract class iToolServiceDataBase : Orleans.Grain, iTool.ClusterComponent.iToolService
    {
        static Dictionary<string, ISqlStructureProvider> TableAndProviderDictionary;
        static iToolServiceDataBase() 
        {
            TableAndProviderDictionary = new Dictionary<string, ISqlStructureProvider>();
        }
        public static ISqlStructureProvider GetSqlStructureProviderByTableName(string tableName)
        {
            if (TableAndProviderDictionary.TryGetValue(tableName, out var result))
            {
                return result;
            }
            else
            {
                lock (TableAndProviderDictionary)
                {
                    if (TableAndProviderDictionary.TryGetValue(tableName, out result))
                    {
                        return result;
                    }
                    else
                    {
                        result = new SqliteStructureProvider(tableName, new SqliteConnectionStringBuilder
                        {
                            //DataSource = $"{DirectoryPath}/{this.TableName}.master",
                            DataSource = DataSource,
                            Mode = SqliteOpenMode.ReadWriteCreate,
                            Cache = SqliteCacheMode.Shared,
                        });

                        TableAndProviderDictionary.TryAdd(tableName, result);
                    }
                }

                return result;
            }
        }

        private KeyGeneratorOptions options;
        private bool IsSyncing = false;
        private Timer IndexSyncTimer;
        protected EventHandler TimerEventHandel;
        protected string TableName { get; set; } = String.Empty;
        protected IKeyGenerator KeyGenInstance { get; set; }
        protected string SqliteConnectionInstanceOfPrivate { get; set; } = String.Empty;
        protected string SqliteConnectionInstanceOfShared { get; set; } = String.Empty;
        protected string SqliteDBVersionConnectionInstanceOfPrivate { get; set; } = String.Empty;

        readonly ClusterOptions clusterOptions;
        const string DirectoryPath = "./Storage";
        const string DataSource = DirectoryPath + "/DataBase.master";
        const string DataSourceVersion = DirectoryPath + "/Versions/{0}.version";
        readonly ISiloStatusOracle siloStatusOracle;


        static AISQLProvider iSQLProvider = new AISQLProvider();


#pragma warning disable CS8618 // 在退出构造函数时，不可为 null 的字段必须包含非 null 值。请考虑声明为可以为 null。
        public iToolServiceDataBase(KeyGeneratorOptions options, IOptions<ClusterOptions> clusterOptions, ISiloStatusOracle siloStatusOracle)
#pragma warning restore CS8618 // 在退出构造函数时，不可为 null 的字段必须包含非 null 值。请考虑声明为可以为 null。
        {
            this.siloStatusOracle = siloStatusOracle;
            this.clusterOptions = clusterOptions.Value;
            options.WorkerId = 0;
            this.options = options;
            if (!Directory.Exists(DirectoryPath))
                Directory.CreateDirectory(DirectoryPath);

            if (!Directory.Exists(DirectoryPath + "/Versions"))
                Directory.CreateDirectory(DirectoryPath + "/Versions");

            // timer
            if (this is iTableExecuteService || this is iTableVersionService)
            {
                this.IndexSyncTimer = new System.Timers.Timer(500);
                this.IndexSyncTimer.Elapsed += new ElapsedEventHandler(this.TimerEvent);
            }

            iSQLProvider.Dispose();
        }

        /// <summary>
        /// 激活事件
        /// </summary>
        public async override Task OnActivateAsync()
        {
            this.TableName = this.GetPrimaryKeyString();
            if (this.TableName.IndexOf('/') > -1)
            {
                this.TableName = this.TableName.Split('/')[1];
            }
            await this.BuilderKeyGeneratorServiceAsync();
            (await this.GetConnectionAsync(null)).Close();
            (await this.GetDBVersionConnectionAsync()).Close();
            if (this is iTableExecuteService || this is iTableVersionService)
            {
                // TODU 开启同步索引 （计时器）
                this.IndexSyncTimer.Start();
            }
            await base.OnActivateAsync();
        }

        /// <summary>
        /// 注销事件
        /// </summary>
        public override Task OnDeactivateAsync()
        {
            this.IndexSyncTimer?.Stop();
            this.IndexSyncTimer?.Dispose();
            return base.OnDeactivateAsync();
        }

        /// <summary>
        /// 获取一个关键字
        /// </summary>
        protected long NextKeyOfLong()
        {
            return this.KeyGenInstance.NextKeyOfLong();
        }

        /// <summary>
        /// 根据关键字查找分区
        /// </summary>
        /// <returns></returns>
        protected int GetPartitionByKey(long key)
        {
            var timeTick = key >> options.WorkerIdBitLength + options.SeqBitLength;
            return (int)(timeTick % options.PartitionCount);
        }

        /// <summary>
        /// 根据关键字查询时间
        /// </summary>
        /// <returns></returns>
        protected DateTime GetTimerByKey(long key)
        {
            var timeTick = key >> options.WorkerIdBitLength + options.SeqBitLength;
            return options.BaseTime.Add(new TimeSpan(timeTick));
        }

        protected async Task IsNeedReBuildIndexByFieldsAsync(List<string>? locations, string[]? fields) 
        {
            IIndexManageService indexManageService = IDirectoryServiceFactory<IndexManageService>.GetService(0, this.TableName);

            if (await indexManageService.IsNeedReBuildIndexByFieldsAsync(locations ?? new List<string>(), fields ?? new string[0]))
            {
                try
                {
                    // 获取写入锁
                    if (iLock<iToolServiceDataBase>.EnterWriteLock(this.TableName))
                    {
                        using (var connection = await this.GetDBVersionConnectionAsync())
                        {
                            string sql = $"update {this.TableName} set isCreateIndex = 1";
                            using (var command = new SqliteCommand(sql, connection))
                            {
                                await command.ExecuteNonQueryAsync();
                            }
                        }
                        // 应该使用非阻塞批更新，索引存在延迟但不影响主线操作
                        await this.ReBuildAllAsync();
                        await this.CommitAsync();
                    }
                }
                finally
                {
                    // 释放写入锁
                    iLock<iToolServiceDataBase>.ExitWriteLock(this.TableName);
                }
            }
        }

        protected async Task<(List<string> locations, List<string> fields)> GetAllIndexFieldsAsync()
        {
            IIndexManageService indexManageService = IDirectoryServiceFactory<IndexManageService>.GetService(0, this.TableName);
            return await indexManageService.GetAllIndexFieldsAsync();
        }

        /// <summary>
        /// 添加索引
        /// </summary>
        /// <returns></returns>
        protected async Task AddIndexAsync(params long[] ids)
        {
            IIndexManageService indexManageService = IDirectoryServiceFactory<IndexManageService>.GetService(0, this.TableName);
            await indexManageService.AddIndexAsync(ids);
        }

        /// <summary>
        /// 修改索引
        /// </summary>
        protected async Task ModifyIndexAsync(params long[] ids)
        {
            IIndexManageService indexManageService = IDirectoryServiceFactory<IndexManageService>.GetService(0, this.TableName);
            await indexManageService.ModifyIndexAsync(ids);
        }

        /// <summary>
        /// 删除索引
        /// </summary>
        protected async Task RemoveIndexAsync(params long[] ids)
        {
            IIndexManageService indexManageService = IDirectoryServiceFactory<IndexManageService>.GetService(0, this.TableName);
            await indexManageService.RemoveIndexAsync(ids);
        }

        /// <summary>
        /// 删除所有索引
        /// </summary>
        protected async Task RemoveAllAsync()
        {
            IIndexManageService indexManageService = IDirectoryServiceFactory<IndexManageService>.GetService(0, this.TableName);
            await indexManageService.RemoveAllAsync();
        }

        /// <summary>
        /// 重新生成索引
        /// </summary>
        protected async Task ReBuildAllAsync()
        {
            IIndexManageService indexManageService = IDirectoryServiceFactory<IndexManageService>.GetService(0, this.TableName);
            await indexManageService.ReBuildAllAsync();
        }

        /// <summary>
        /// 是否存在索引 
        /// 或者判断是否包含索引 fields 不为空时
        /// </summary>
        protected async Task<bool> IsHasIndexAsync(params string[] fields)
        {
            IIndexManageService indexManageService = IDirectoryServiceFactory<IndexManageService>.GetService(0, this.TableName);
            return await indexManageService.IsHasIndexAsync(fields);
        }

        /// <summary>
        /// 索引回滚
        /// </summary>
        protected async Task RollbackAsync()
        {
            IIndexManageService indexManageService = IDirectoryServiceFactory<IndexManageService>.GetService(0, this.TableName);
            await indexManageService.RollbackAsync();
        }

        /// <summary>
        /// 索引提交
        /// </summary>
        protected async Task CommitAsync()
        {
            IIndexManageService indexManageService = IDirectoryServiceFactory<IndexManageService>.GetService(0, this.TableName);
            await indexManageService.CommitAsync();
        }

        /// <summary>
        /// 格式化Sql 生成描述信息
        /// </summary>
        protected async Task<AnalysisExecuteItemOptions> GetFormatSqlAsync(string sql, SqliteParameter[] parameters) 
        {
            AnalysisResult analysisResult = iSQLProvider.GetAnalysisResult(sql);
            var query = new AnalysisExecuteItemOptions(analysisResult, parameters);
            await this.GetFormatSqlAsync(query);
            return query;
        }

        /// <summary>
        /// 格式化Sql 生成描述信息
        /// </summary>
        protected async Task<List<SearchResponse>> GetFormatSqlAsync(AnalysisExecuteItemOptions executeItemOptions)
        {
            await this.CheckStructureAsync(executeItemOptions.TableStructures);

            List<SearchResponse> results = new List<SearchResponse>();
            string whereCase = executeItemOptions.WhereCase;
            bool isUseIndex = false;
            if (executeItemOptions.Indexs?.Any() == true)
            {
                Parallel.ForEach(executeItemOptions.Indexs, async index =>
                {
                    try
                    {
                        List<string> indexFieldArray = new List<string>();
                        List<string> locationFieldArray = new List<string>();
                        foreach (var item in index.ChildIndexFns)
                        {

                            if (new IndexModeOptions[] { IndexModeOptions.ShoubeDistMap, IndexModeOptions.MustDistMap, IndexModeOptions.MustNotDistMap }.Contains(item.IndexMode))
                            {
                                if (item.Fields.Count != 2)
                                    throw new Exception("Distance calculation must have 2 fields");
                                locationFieldArray.Add(string.Join('&', item.Fields));
                            }
                            else
                            {
                                indexFieldArray.AddRange(item.Fields);
                            }
                        }

                        var orderBy = executeItemOptions.SortByOptions?.Where(item => item.TableName == index.Table).FirstOrDefault();

                        if (orderBy != null)
                        {
                            // 如果按照地理排序 则处理查询SQL
                            if (orderBy.IsSortDist)
                            {
                                if (orderBy.Fields.Count != 2)
                                    throw new Exception("Order By: Distance calculation must have 2 fields");

                                locationFieldArray.Add(string.Join('&', orderBy.Fields));
                                executeItemOptions.QuerySql = executeItemOptions.QuerySql.Replace(orderBy.OrderCase, string.Empty);
                            }
                            else
                            {
                                if (orderBy.Fields.Any())
                                {
                                    indexFieldArray.AddRange(orderBy.Fields);
                                }
                            }
                        }

                        indexFieldArray = indexFieldArray.Distinct().ToList();
                        locationFieldArray = locationFieldArray.Distinct().ToList();


                        // check index or build
                        //IIndexManageService indexManageService = this.GrainFactory.GetGrain<IIndexManageService>(tableName);
                        IIndexManageService indexManageService = IDirectoryServiceFactory<IndexManageService>.GetService(0, index.Table);

                        if (await indexManageService.IsNeedReBuildIndexByFieldsAsync(locationFieldArray, indexFieldArray.ToArray()))
                        {
                            try
                            {
                                // 获取写入锁
                                if (iLock<iToolServiceDataBase>.EnterWriteLock(this.TableName))
                                {
                                    using (var connection = await this.GetDBVersionConnectionAsync())
                                    {
                                        string sql = $"update {this.TableName} set isCreateIndex = 1";
                                        using (var command = new SqliteCommand(sql, connection))
                                        {
                                            await command.ExecuteNonQueryAsync();
                                        }
                                    }
                                    await this.ReBuildAllAsync();
                                    await this.CommitAsync();
                                }
                            }
                            finally
                            {
                                // 释放写入锁
                                iLock<iToolServiceDataBase>.ExitWriteLock(this.TableName);
                            }
                        }

                        // reader
                        IIndexReaderService indexReaderService = IDirectoryServiceFactory<IndexReaderService>.GetService(0, index.Table);
                        //IIndexReaderService indexReaderService = this.GrainFactory.GetGrain<IIndexReaderService>(tableName);

                        //long[] idx = await indexReaderService.MultipleConditionsAsync(index, orderBy, index.ChildIndexFns.ToArray());
                        var response = await indexReaderService.MultipleConditionsAsync(index, orderBy?.Fields.Any() == true ? orderBy : null, index.ChildIndexFns.ToArray());
                        results.Add(response);

                        if (response.Keys.Any())
                        {
                            isUseIndex = true;
                            if (index.IsHasNot)
                            {
                                // not in
                                executeItemOptions.QuerySql = executeItemOptions.QuerySql.Replace(index.Sql, $"_IDX NOT IN ({String.Join(',', response.Keys)})");
                                executeItemOptions.WhereCase = executeItemOptions.WhereCase.Replace(index.Sql, $"_IDX NOT IN ({String.Join(',', response.Keys)})");
                            }
                            else
                            {
                                // in
                                executeItemOptions.QuerySql = executeItemOptions.QuerySql.Replace(index.Sql, $"_IDX IN ({String.Join(',', response.Keys)})");
                                executeItemOptions.WhereCase = executeItemOptions.WhereCase.Replace(index.Sql, $"_IDX IN ({String.Join(',', response.Keys)})");
                            }
                        }
                        else
                        {
                            executeItemOptions.QuerySql = executeItemOptions.QuerySql.Replace(index.Sql, String.Intern("1=2"));
                            executeItemOptions.WhereCase = executeItemOptions.WhereCase.Replace(index.Sql, String.Intern("1=2"));
                        }

                        if (orderBy?.Fields.Any() == true)
                        {
                            executeItemOptions.QuerySql = executeItemOptions.QuerySql.Replace(orderBy.OrderCase, string.Empty);
                        }
                    }
                    catch (Exception ex)
                    {
                        //logger
                        Console.WriteLine("error:" + ex.Message);
                        executeItemOptions.Error = ex.Message;
                    }
                });
            }

            if (!string.IsNullOrWhiteSpace(executeItemOptions.Error))
            {
                throw new Exception(executeItemOptions.Error);
            }

            if (executeItemOptions.Action == SQLActionOptions.SELECT || executeItemOptions.Action == SQLActionOptions.INSERT)
            {
                executeItemOptions.Keys = new long[0];
            }
            else
            {
                List<long> keys = new List<long>();
                if (string.IsNullOrWhiteSpace(executeItemOptions.WhereCase))
                {
                    // 没有条件则是全表
                    keys.Add(-1);
                    if (!string.IsNullOrWhiteSpace(whereCase))
                    {
                        executeItemOptions.QuerySql = executeItemOptions.QuerySql.Replace(whereCase, string.Empty);
                    }
                }
                else
                {
                    string query = string.Format("select _IDX from {0} {1}", this.TableName, executeItemOptions.WhereCase);
                    await using (var connection = await this.GetConnectionAsync(executeItemOptions.CustomFunctions, isUseIndex))
                    {
                        await connection.OpenAsync();
                        await using (SqliteCommand command = new SqliteCommand(query, connection))
                        {
                            await using (var reader = await command.ExecuteReaderAsync())
                            {
                                while (reader.Read())
                                {
                                    long key = reader.GetInt64(0);
                                    keys.Add(key);
                                }
                            }
                        }
                    }

                    if (keys.Any())
                    {
                        executeItemOptions.QuerySql = executeItemOptions.QuerySql.Replace(whereCase, $"where _IDX IN ({String.Join(',', keys)})");
                    }
                }

                executeItemOptions.Keys = keys.ToArray();
                // 虽然生成了Key 但是执行的还是原来的Sql
            }

            return results;
        }

        protected async Task CheckStructureAsync(Dictionary<string, LinkedList<string>> keyValuePairs)
        {
            foreach (var item in keyValuePairs)
            {
                await GetSqlStructureProviderByTableName(item.Key).CheckStructureAsync(item.Value);
            }
        }

        #region Builder

        private async Task BuilderKeyGeneratorServiceAsync()
        {

            var workerKey = string.Format("{0}_{1}", clusterOptions.ClusterId, clusterOptions.ServiceId);
            string filePath = $"./workerids/{workerKey}.id";
            if (System.IO.File.Exists(filePath))
            {
                var content = await File.ReadAllTextAsync(filePath);
                if (ushort.TryParse(content, out ushort workerId))
                {
                    options.WorkerId = workerId;
                }
            }
            else
            {
                if (!Directory.Exists(Path.GetDirectoryName(filePath)))
                {
                    Directory.CreateDirectory(Path.GetDirectoryName(filePath));
                }
            }

            if (options.WorkerId == 0)
            {
                var service = base.GrainFactory.GetGrain<IKeyWorkerService>(workerKey);
                options.WorkerId = await service.GetWorkerIdAsync();
                await File.WriteAllTextAsync(filePath, options.WorkerId.ToString());
            }

            int maxWorkerIdNumber = (1 << options.WorkerIdBitLength) - 1;

            // 超出最大worker id
            Console.WriteLine(String.Format("最大 WorkerId 为：{0}, 当前WorkerId:{1}", maxWorkerIdNumber, options.WorkerId));

            this.KeyGenInstance = new DefaultGeneratorProvider(new UniqueKeyGeneratorOptions
            {
                WorkerId = options.WorkerId,//options.WorkerId, // 需要唯一
                WorkerIdBitLength = options.WorkerIdBitLength,
                SeqBitLength = options.SeqBitLength,
                //DataCenterIdBitLength = 3,
                TopOverCostCount = 2000,
                BaseTime = options.BaseTime
            });
        }

        protected async Task<SqliteConnection> GetConnectionAsync(List<string>? customFunctions, bool isUsingIndex = true) 
        {
            //https://learn.microsoft.com/zh-cn/dotnet/standard/data/sqlite/connection-strings
            // 缓存：cache = shared | private
            // 不可变(只读): immutable = 1 | 0
            // mode = ro | rw | rwc | memory

            SqliteConnection result;

            if (isUsingIndex)
            {
                if (string.IsNullOrWhiteSpace(this.SqliteConnectionInstanceOfShared))
                {
                    this.SqliteConnectionInstanceOfShared = new SqliteConnectionStringBuilder
                    {
                        DataSource = DataSource,
                        Mode = SqliteOpenMode.ReadWriteCreate,
                        //Mode = this is iTableReaderService ? SqliteOpenMode.ReadOnly : SqliteOpenMode.ReadWriteCreate,
                        Cache = this is iTableReaderService ? SqliteCacheMode.Shared : SqliteCacheMode.Private,
                        Pooling = true
                    }.ToString();
                }

                result = new SqliteConnection(this.SqliteConnectionInstanceOfShared);
            }
            else
            {
                if (string.IsNullOrWhiteSpace(this.SqliteConnectionInstanceOfPrivate))
                {
                    this.SqliteConnectionInstanceOfPrivate = new SqliteConnectionStringBuilder
                    {
                        DataSource = DataSource,
                        Mode = SqliteOpenMode.ReadWriteCreate,
                        //Mode = this is iTableReaderService ? SqliteOpenMode.ReadOnly : SqliteOpenMode.ReadWriteCreate,
                        Cache = SqliteCacheMode.Private,
                        Pooling = true
                    }.ToString();
                }
                result = new SqliteConnection(this.SqliteConnectionInstanceOfPrivate);
            }

            if (result.State != System.Data.ConnectionState.Open)
            {
                await result.OpenAsync();
            }

            if (customFunctions?.Any() == true)
            {
                var structure = new CustomFunctionStructure(result, customFunctions);
                structure.Excuter();
            }

            return result;
        }

        #endregion


        #region DB Version

        /// <summary>
        /// 添加增删改Logger
        /// </summary>
        protected async Task AddExecLoggerAsync(IEnumerable<ExecLogger> loggers)
        {
            iLock<iToolServiceDataBase>.EnterReadLock(this.TableName);
            try
            {
                await using (var connection = await this.GetDBVersionConnectionAsync())
                {
                    await using (var transaction = await connection.BeginTransactionAsync(System.Data.IsolationLevel.ReadUncommitted))
                    {
                        try
                        {
                            await using (var command = new SqliteCommand(String.Intern($"insert into {this.TableName}(action,sql,keys,extend,tranid,isCreateIndex) values($action,$sql,$keys,$extend,$tranid,0)"), connection))
                            {
                                command.Transaction = (SqliteTransaction)transaction;
                                // 是跟 connection 的， 如果已经执行过 Prepare，后续调用会直接返回 不会有其他影响
                                //command.Prepare();
                                var actionParams = new SqliteParameter { ParameterName = "$action" };
                                var sqlParams = new SqliteParameter { ParameterName = "$sql" };
                                var keysParams = new SqliteParameter { ParameterName = "$keys" };
                                var extendParams = new SqliteParameter { ParameterName = "$extend" };
                                var tranidParams = new SqliteParameter { ParameterName = "$tranid" };
                                command.Parameters.Add(actionParams);
                                command.Parameters.Add(sqlParams);
                                command.Parameters.Add(keysParams);
                                command.Parameters.Add(extendParams);
                                command.Parameters.Add(tranidParams);
                                foreach (var item in loggers)
                                {
                                    actionParams.Value = item.action.ToString();
                                    sqlParams.Value = item.action == SQLActionOptions.DELETE ? string.Empty : item.sql;
                                    keysParams.Value = string.Join(',', item.keys);
                                    extendParams.Value = item.extend;
                                    tranidParams.Value = item.tranId;
                                    await command.ExecuteNonQueryAsync();
                                }
                            }
                            await transaction.CommitAsync();
                        }
                        catch (Exception ex)
                        {
                            await transaction.RollbackAsync();
                            throw ex;
                        }
                    }
                }

                // 不能跨线程，否则无法释放
                iLock<iToolServiceDataBase>.ExitReadLock(this.TableName);


                IClusterService clusterService = iBox.GetService<IClusterService>("IClusterService");
                // 广播通知更新
                var management = clusterService.GetManagementer();
                var result = await management.GetHosts(true);
                var noders = result
                    .Where(item => !item.Key.Equals(this.siloStatusOracle.SiloAddress))
                    .Select(item => clusterService.GetService<iTableVersionService>(string.Format("{0}/{1}", item.Key.ToParsableString(), this.TableName)));

                await Task.WhenAll(noders.Select(item => item.AcceptNotificationAsync()));
            }
            finally
            {
                iLock<iToolServiceDataBase>.ExitReadLock(this.TableName);
            }
        }

        private async void TimerEvent(object sender, ElapsedEventArgs e)
        {
            try
            {
                await this.IndexSyncEventAsync(sender, e);
                if (this.TimerEventHandel != null)
                {
                    TimerEventHandel.Invoke(sender, e);
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }

        /// <summary>
        /// 获取Open的LoggerDB
        /// </summary>
        protected async Task<SqliteConnection> GetDBVersionConnectionAsync()
        {
            SqliteConnection result;

            if (string.IsNullOrWhiteSpace(this.SqliteDBVersionConnectionInstanceOfPrivate))
            {
                this.SqliteDBVersionConnectionInstanceOfPrivate = new SqliteConnectionStringBuilder
                {
                    DataSource = String.Format(DataSourceVersion,this.TableName),
                    Mode = SqliteOpenMode.ReadWriteCreate,
                    Cache = SqliteCacheMode.Private,
                    Pooling = true
                }.ToString();
            }

            result = new SqliteConnection(this.SqliteDBVersionConnectionInstanceOfPrivate);

            if (result.State != System.Data.ConnectionState.Open)
            {
                await result.OpenAsync();
            }

            return result;
        }

        /// <summary>
        /// 根据Logger 同步索引事件
        /// </summary>
        private async Task IndexSyncEventAsync(object sender, ElapsedEventArgs e) 
        {

            // -- 如果没有Keys  可以不用写入Delete表, 这边判断Keys是否存在;（使用 -1）
            if (this.IsSyncing) {
                return;
            }
            else
            {
                this.IsSyncing = true;
                // 此表没有索引
                if (!await this.IsHasIndexAsync())
                {
                    this.IsSyncing = false;
                    return;
                }
            }

            try
            {
                List<long> rows = new List<long>();
                List<(string action, string keys, string extend)> loggers = new List<(string action, string keys, string extend)>();

                if (iLock<iToolServiceDataBase>.EnterWriteLock(this.TableName))
                {
                    using (var connection = await this.GetDBVersionConnectionAsync())
                    {
                        using (var command = new SqliteCommand(String.Intern($"select action,keys,extend,rowid from {this.TableName} where isCreateIndex=0 limit 1000"), connection))
                        {
                            var reader = command.ExecuteReader();
                            if (reader.HasRows)
                            {
                                while (reader.Read())
                                {
                                    rows.Add(reader.GetInt64(3));
                                    loggers.Add((reader.GetString(0), reader.GetString(1), reader.GetString(2)));
                                }
                            }
                        }
                    }
                }
                else
                {
                    return;
                }

                

                if (!rows.Any())
                {
                    return;
                }

                foreach (var item in loggers)
                {
                    if (string.Equals(item.action, "UPDATE"))
                    {
                        // extend is set fields
                        if (!string.IsNullOrEmpty(item.keys))
                        {
                            string[] fields = item.extend.Split(',');
                            // set field 是索引字段才会 去更新
                            if (await this.IsHasIndexAsync(fields))
                            {
                                long[] keys = item.keys.Split(',').Select(item => long.Parse(item)).ToArray();
                                if (keys[0] == -1)
                                {
                                    await this.ReBuildAllAsync();
                                }
                                else
                                {
                                    await this.ModifyIndexAsync(keys);
                                }
                            }
                        }
                    }
                    else if (string.Equals(item.action, "INSERT"))
                    {
                        long key = long.Parse(item.keys);
                        await this.AddIndexAsync(key);
                    }
                    else if (string.Equals(item.action, "DELETE"))
                    {
                        if (!string.IsNullOrEmpty(item.keys))
                        {
                            long[] keys = item.keys.Split(',').Select(item => long.Parse(item)).ToArray();
                            if (keys[0] == -1)
                            {
                                await this.RemoveAllAsync();
                            }
                            else
                            {
                                await this.RemoveIndexAsync(keys);
                            }
                        }
                    }
                }

                await this.CommitAsync();


                iLock<iToolServiceDataBase>.ExitWriteLock(this.TableName);

                using (var connection = await this.GetDBVersionConnectionAsync())
                {
                    string sql = $"update {this.TableName} set isCreateIndex = 1 where rowid in ({string.Join(',', rows)})";
                    using (var command = new SqliteCommand(sql, connection))
                    {
                        await command.ExecuteNonQueryAsync();
                    }
                }

            }
            finally
            {
                iLock<iToolServiceDataBase>.ExitWriteLock(this.TableName);
                this.IsSyncing = false;
            }
        }

        #endregion

    }

}
